package io.potato.ts.service;

import io.potato.core.AppException;
import io.potato.ts.common.Constants;
import io.potato.ts.domain.SmsSendedStat;
import io.potato.ts.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;


/**
 * 已发送短信统计服务类
 * 
 * @author timl
 *
 * <p>2019-04-11 09:38:13</p>
 */
@Service
@Transactional
public class SmsSendedStatService {

	@Autowired
	UserService userService;

	@Autowired
	JdbcTemplate jdbcTemplate;

	public List<SmsSendedStat> statSmsSendedByOrgan(Integer organId, Integer smsType, Integer year, Integer month) {

		String sql;
		String statColumn = "stat_year, stat_month, \n" +
		"sum(total), case when sum(failed) > 200 then sum(total) - ROUND(sum(failed) * 0.8, 0) else sum(success) end success," +
				" case when sum(failed) > 200 then ROUND(sum(failed) * 0.8, 0) else sum(failed) end failed, \n" +
				"sum(total1), sum(success1), sum(failed1), \n" +
				"sum(total2), sum(success2), sum(failed2), \n" +
				"sum(total3), sum(success3), sum(failed3) \n";
		String where = " WHERE 1 = 1 \n";
		String groupBy;

		if (Constants.SENDER_TYPE_API.equals(smsType)) {
			sql = "SELECT " + statColumn
					+ " ,t1.user_name name FROM sms_sended_stat t \n"
					+ " JOIN  t_user t1 \n"
					+ " on t.user_code = t1.user_code \n";
			groupBy = " \nGROUP BY t.user_code, stat_year, stat_month " +
					"\norder by user_name, stat_year, stat_month";
			where += " AND t.user_type = 1 ";
		} else {
			sql = "SELECT " + statColumn
					+ " ,t1.name FROM sms_sended_stat t \n"
					+ " JOIN  t_organ t1 \n"
					+ " on t.organ_id = t1.id \n";
			if (organId != null && organId > 0) {
				where += " AND t.organ_id = " + organId;
			}
			where += " AND t.user_type = 2 ";
			groupBy = " \nGROUP BY organ_id, stat_year, stat_month " +
					"\norder by name, stat_year, stat_month";
		}

		if (year != null && year > 0) {
			where += " AND t.stat_year = " + year;
		}

		if (month != null && month > 0) {
			where += " AND t.stat_month = " + month;
		}

		sql = sql + where + groupBy;

		List<SmsSendedStat> list = jdbcTemplate.query(sql, new StatRowMapper());

		addTotalRow(list);

		return list;
	}

	public List<SmsSendedStat> statSmsSended(Integer organId, Integer deptId, Integer userId, Integer year, Integer month) {
		String sql = "SELECT stat_year, stat_month, \n" +
				"sum(total), sum(success), sum(failed), \n" +
				"sum(total1), sum(success1), sum(failed1), \n" +
				"sum(total2), sum(success2), sum(failed2), \n" +
				"sum(total3), sum(success3), sum(failed3), '-' name \n" +
				"FROM sms_sended_stat t \n";

		if (deptId != null && deptId > 0) {
			sql += " JOIN  t_user t1 \n" +
					"on t.user_code = t1.user_code AND t1.dept_id = " + deptId;
		}

		sql += " WHERE 1 = 1 \n";

		if (organId != null && organId > 0) {
			sql += " AND t.organ_id = " + organId;
		}

        if (userId != null && userId > 0) {
        	Optional<User> optionalUser = userService.findById(userId);
        	if (!optionalUser.isPresent()) {
        		throw new AppException("用户不存在");
			}
            sql += " AND t.user_code = '" + optionalUser.get().getUserCode() + "' ";
        }

		if (year != null && year > 0) {
			sql += " AND t.stat_year = " + year;
		}

		if (month != null && month > 0) {
			sql += " AND t.stat_month = " + month;
		}

		sql += " \nGROUP BY stat_year, stat_month " +
				"\norder by stat_year, stat_month";

		List<SmsSendedStat> list = jdbcTemplate.query(sql, new StatRowMapper());

		addTotalRow(list);

		return list;
	}

	static class StatRowMapper implements RowMapper<SmsSendedStat> {

		@Override
		public SmsSendedStat mapRow(ResultSet rs, int rowNum) throws SQLException {
			SmsSendedStat stat = new SmsSendedStat();

			stat.setStatYear(rs.getInt(1));
			stat.setStatMonth(rs.getInt(2));

			stat.setTotal(rs.getInt(3));
			stat.setSuccess(rs.getInt(4));
			stat.setFailed(rs.getInt(5));

			stat.setTotal1(rs.getInt(6));
			stat.setSuccess1(rs.getInt(7));
			stat.setFailed1(rs.getInt(8));

			stat.setTotal2(rs.getInt(9));
			stat.setSuccess2(rs.getInt(10));
			stat.setFailed2(rs.getInt(11));

			stat.setTotal3(rs.getInt(12));
			stat.setSuccess3(rs.getInt(13));
			stat.setFailed3(rs.getInt(14));
			stat.setName(rs.getString(15));
			return stat;
		}
	}

	private void addTotalRow(List<SmsSendedStat> list) {
		if (list == null || list.isEmpty()) {
			return;
		}
		SmsSendedStat totalRow = new SmsSendedStat();
		totalRow.setStatYear(0);
		totalRow.setStatMonth(0);
		totalRow.setTotal(0);
		totalRow.setSuccess(0);
		totalRow.setFailed(0);
		totalRow.setTotal1(0);
		totalRow.setSuccess1(0);
		totalRow.setFailed1(0);
		totalRow.setTotal2(0);
		totalRow.setSuccess2(0);
		totalRow.setFailed2(0);
		totalRow.setTotal3(0);
		totalRow.setSuccess3(0);
		totalRow.setFailed3(0);

		for (SmsSendedStat row : list) {
			totalRow.setTotal(totalRow.getTotal() + row.getTotal());
			totalRow.setSuccess(totalRow.getSuccess() + row.getSuccess());
			totalRow.setFailed(totalRow.getFailed() + row.getFailed());

			totalRow.setTotal1(totalRow.getTotal1() + row.getTotal1());
			totalRow.setSuccess1(totalRow.getSuccess1() + row.getSuccess1());
			totalRow.setFailed1(totalRow.getFailed1() + row.getFailed1());

			totalRow.setTotal2(totalRow.getTotal2() + row.getTotal2());
			totalRow.setSuccess2(totalRow.getSuccess2() + row.getSuccess2());
			totalRow.setFailed2(totalRow.getFailed2() + row.getFailed2());

			totalRow.setTotal3(totalRow.getTotal3() + row.getTotal3());
			totalRow.setSuccess3(totalRow.getSuccess3() + row.getSuccess3());
			totalRow.setFailed3(totalRow.getFailed3() + row.getFailed3());
		}

		list.add(totalRow);
	}
	
}
